﻿IF EXISTS (
    SELECT
      1
    FROM sysobjects
    WHERE id = OBJECT_ID('bd_proc_personal_schedule')
      AND type IN ('P', 'PC')
  )
  DROP PROCEDURE bd_proc_personal_schedule

GO


-- =============================================
-- Author:      YFC
-- Create date: 2020-07-13 16:05:58
-- Database:    scm_main_hysj20200702
-- Description: 个人中心-我的排班信息查询
-- =============================================

CREATE PROCEDURE bd_proc_personal_schedule
  @userId BIGINT,
  @year INT,
  @month  INT
AS
BEGIN
  SET NOCOUNT ON;

  CREATE TABLE #result
  (
    title VARCHAR(300) COLLATE Chinese_PRC_CI_AS
    , [start] DATETIME
    , [end] DATETIME
    , dayType INT
    , display VARCHAR(300) COLLATE Chinese_PRC_CI_AS
    , color VARCHAR(50) COLLATE Chinese_PRC_CI_AS
  )

  CREATE TABLE #temp
  (
    title VARCHAR(300) COLLATE Chinese_PRC_CI_AS
    , [start] DATETIME
    , start_date  DATE
    , [end] DATETIME
    , end_date  DATE
    , dayType INT
    , display VARCHAR(300) COLLATE Chinese_PRC_CI_AS
    , color VARCHAR(50) COLLATE Chinese_PRC_CI_AS
    
  )

  DECLARE @work_id  INT
  --TODO
  SET @work_id = 1

  --工作日历排班
  INSERT INTO #temp(title, start, start_date, [end], dayType, display, color)
  SELECT bc.mc + '  ' + dbo.bd_fn_numToMin3(bc.sbsj) + '-' + dbo.bd_fn_numToMin3(bc.xbsj) title,
   dbo.bd_fn_minToDateTime(bc.sbsj, wk.work_date) start,
   wk.work_date,
   dbo.bd_fn_minToDateTime(bc.xbsj, wk.work_date) [end],
   CASE WHEN wk.is_weekend = 1 THEN 1 WHEN wk.is_statutory = 1 THEN 2 ELSE 0 END,
   'block' display, CASE WHEN wk.is_weekend = 1 OR wk.is_statutory = 1 THEN '' ELSE '#1577FF' END color
  FROM bd_kq_work_calendar_detail wk
  INNER JOIN bd_view_bc_sd bc ON bc.bc_id = wk.shift_id
  WHERE parent_id = 1 AND work_year = @year AND work_month = @month

  --休息日
  INSERT INTO #temp(title, start, start_date, [end], dayType, display, color)
  SELECT '' title,
   work_date start,
   wk.work_date,
   work_date [end],
   CASE WHEN wk.is_weekend = 1 THEN 1 WHEN wk.is_statutory = 1 THEN 2 ELSE 0 END,
   'background' display, 
    CASE WHEN wk.is_weekend = 1 THEN '' WHEN wk.is_statutory = 1 THEN '#FFCC00' ELSE '' END color
  FROM bd_kq_work_calendar_detail wk
  WHERE parent_id = 1 AND work_year = @year AND work_month = @month AND (wk.is_weekend = 1 OR wk.is_statutory = 1)


--    SELECT * FROM bd_kq_work_calendar_detail
--      WHERE work_year=2020 AND work_month=6 AND parent_id =1

  --班次
--        { //默认为工作日时的数据结构
--          title: "早班", //班次名称，事件名称
--          start: "2020-07-15", //事件开始时间
--          dayType: 1,    //0休息日, 1工作日, 2节假日
--          display: "block", //条状
--          color: "#1577FF" //定义背景颜色
--        },

  UPDATE tmp
  SET title = bc.mc + '  ' + dbo.bd_fn_numToMin3(bc.sbsj) + '-' + dbo.bd_fn_numToMin3(bc.xbsj),
    start = dbo.bd_fn_minToDateTime(bc.sbsj, bc.shift_date),
    start_date = bc.shift_date,
    [end] = dbo.bd_fn_minToDateTime(bc.xbsj, bc.shift_date),
    display = 'block',
    color = '#1577FF'
  FROM #temp tmp
  INNER JOIN (
      SELECT
        card.shift_id, card.shift_date, bc.*
      FROM bd_kq_schedule_detail card
    INNER JOIN bd_view_bc_sd bc ON bc.bc_id = card.shift_id AND bc.lx = 0
      WHERE card.user_serial = @userId AND YEAR(card.shift_date) = @year AND MONTH(card.shift_date) = @month
    ) bc ON bc.shift_date = tmp.start_date

--  INSERT INTO #temp(title, start, start_date, [end], display, color)
--  SELECT bc.mc + '  ' + dbo.bd_fn_numToMin3(bc.sbsj) + '-' + dbo.bd_fn_numToMin3(bc.xbsj) title,
--   dbo.bd_fn_minToDateTime(bc.sbsj, card.shift_date) start,
--    card.shift_date,
--   dbo.bd_fn_minToDateTime(bc.xbsj, card.shift_date) [end],
--
--   'block' display, '#1577FF' color
--  FROM bd_kq_schedule_detail card
--  INNER JOIN bd_view_bc_sd bc ON bc.bc_id = card.shift_id AND bc.lx = 0
--  WHERE card.user_serial = 20001669 AND YEAR(card.shift_date) = 2020 AND MONTH(card.shift_date) = 6


--SELECT user_serial,COUNT(1) qty FROM bd_kq_schedule_detail 
--  WHERE YEAR(shift_date) = 2020 AND MONTH(shift_date) =6
--GROUP BY user_serial


--SELECT * FROM bd_kq_schedule_detail

  --请假
--        {   //请假事件的数据结构
--          title: "请假",
--          start: "2020-07-08",
--          dayType: 1,    //0休息日, 1工作日, 2节假日
--          display: "block", //条状
--          color: "#FFB51F"   //请假颜色
--        },
--  SELECT * FROM bd_leave_order 
--  SELECT * FROM lr_kqingj

  INSERT INTO #temp(title, start, start_date, [end], display, color)
  SELECT '请假  ' + leave.begin_time + '-' + leave.end_time title, detail.kssj start, detail.kssj start_date, detail.jssj [end], 'block' display, '#FFB51F' color
  FROM lr_kqingj detail
  INNER JOIN bd_leave_order leave ON leave.order_id = detail.order_id
  WHERE user_serial = @userId


  --加班
--         {   //加班事件的数据结构
--          title: "加班",
--          start: "2020-07-08",
--          dayType: 1,    //0休息日, 1工作日, 2节假日
--          display: "block", //条状
--          color: "#33CC00"  //加班颜色
--        },
--SELECT * FROM lr_jiaba 
--ORDER BY user_serial, jssj

  INSERT INTO #temp(title, start, start_date, [end], dayType, display, color)
  SELECT '加班  ' + dbo.bd_fn_numToMin3(detail.ot_begin) + '-' + dbo.bd_fn_numToMin3(detail.ot_end) title, 
    DATEADD(MONTH, -1, ot.ot_date), 
    ot.ot_date, DATEADD(MONTH, -1, ot.ot_date), 
    CASE WHEN wk.is_weekend = 1 THEN 1 WHEN wk.is_statutory = 1 THEN 2 ELSE 0 END,
    'block' display,
    '#33CC00' color
  FROM lr_jiaba detail
  INNER JOIN bd_over_time_order ot ON ot.order_id = detail.order_id
    INNER JOIN (SELECT * FROM bd_kq_work_calendar_detail WHERE parent_id = @work_id) wk ON wk.work_date = ot.ot_date
  WHERE detail.user_serial = @userId AND YEAR(ot.ot_date) = @year AND MONTH(ot.ot_date) = @month

  --打卡异常
--        {   //迟到、早退、旷工事件的数据结构
--          title: "早退",   
--          start: "2020-07-13",
--          dayType: 1,    //0休息日, 1工作日, 2节假日
--          display: "block", //条状
--          color: "#FE4E1D"    //迟到、早退、旷工颜色
--        },
  INSERT INTO #temp(title, start, start_date, [end], display, color)
  SELECT CASE
      card.status
     WHEN 4 THEN
       '迟到'
     WHEN 5 THEN
       '早退'
     WHEN 6 THEN
       '缺勤'
   END + '  ' + dbo.bd_fn_numToMin3(card.shift_begin) + '-' + dbo.bd_fn_numToMin3(card.shift_end) title,
   card.card_date, card.card_date, card.card_date, 'block' display, '#FE4E1D' color
  FROM bd_kq_timecard card
  WHERE card.user_serial = @userId AND card.card_year = @year AND card.card_month = @month AND status IN (4, 5, 6)

--SELECT * FROM bd_kq_timecard WHERE status IN (4,5,6)


--SELECT * FROM #temp t
--
--  UPDATE tmp SET dayType = CASE WHEN w.is_statutory = 1 THEN 2 WHEN w.is_weekend = 1 THEN 1 ELSE 0 END
--    FROM #temp tmp
--    INNER JOIN(
--  SELECT * FROM bd_kq_work_calendar_detail WHERE parent_id = @work_id) w ON w.work_date = tmp.start_date
--
--  UPDATE #temp SET color = '' WHERE dayType > 0

--  SELECT * FROM bd_kq_work_calendar_detail WHERE parent_id=1 AND work_year = 2020 AND work_month =6

  INSERT INTO #result (title, start, [end], dayType, display, color)
  SELECT title, start, [end], ISNULL(dayType, 0), display, color FROM #temp 


  SELECT * FROM #result 

  DROP TABLE #result


  SET NOCOUNT OFF;
END